﻿using System;
using System.Data;
using System.Collections;
using System.Collections.Specialized;
using WP.DB;
using WP.IM.DataEntity;
using WP.Commons;

namespace WP.IM.DataAccess
{
    public class ImInvMasterDAL : IDataAccess
    {
        #region Select List
        public string SQLList(int iParameter)
        {
            string strSQL = @"SELECT RecId,MaterialType,SecondMaterialType,ItemNo,ItemName,SupNo,SupName,MarketCost,CostUnit,TreatCost,TreatCostUnit,PlanTreatCost,PlanCost,PriceFormulaValue,Remark,FItemID FROM ImInvMaster WHERE ClosedStatus=0 order by ItemNo ";
            return strSQL;
        }
        #endregion

        #region Fields
        public string Fields
        {
            get
            {
                return @" FItemID,ItemNo,ItemName,SupNo,SupName,MarketCost,CostUnit,PlanCost,ProdLength,ProdWidth,ProdHeight,Remark,PriceFormula,TreatCost,
                          TreatCostUnit,MaterialType,MaterialTypeID,SecondMaterialType,SecondMaterialTypeID,GramWeight,PaperHeight,ClosedStatus,PlanTreatCost,
                          PriceFormulaValue,IsAddByPDM ";
            }
        }
        #endregion


        private bool CreatTmpTable(DBAccess dbcon)
        {
            string strSQL = "";
            string strSQLTmp = "";

            DataFunctions.DelTemporyTable(dbcon, "#rmWKCalDict");
            strSQL = @" CREATE TABLE [#rmWKCalDict](
	                    [RecID] [int] IDENTITY(1,1) NOT NULL,
	                    [SeqNo] [int] NOT NULL  DEFAULT ((0)),
	                    [RecStamp] [timestamp] NULL,
	                    [CHNFieldName] [varchar](50) DEFAULT ('‘’'),
	                    [SYSFieldName] [varchar](50) DEFAULT (''),
	                    [SYSTableName] [varchar](50) NOT NULL DEFAULT (''),
	                    [Type] [varchar](50) NOT NULL  DEFAULT (''),
	                    [Description] [varchar](500)  NOT NULL  DEFAULT ('')
                    )";
            dbcon.ExecuteSql(strSQL);

            strSQL = @" INSERT INTO #rmWKCalDict( CHNFieldName,Description,SysFieldName,SysTableName)
                       VALUES ('{0}','{1}','{2}','{3}')";
            strSQLTmp = string.Format(strSQL, "常用单价", "", "TreatCost", "Iminvmaster");
            dbcon.ExecuteSql(strSQLTmp);
            strSQLTmp = string.Format(strSQL, "长度", "", "prodLength", "Iminvmaster");
            dbcon.ExecuteSql(strSQLTmp);
            strSQLTmp = string.Format(strSQL, "宽度", "", "prodWidth", "Iminvmaster");
            dbcon.ExecuteSql(strSQLTmp);
            strSQLTmp = string.Format(strSQL, "高度", "", "prodHeight", "Iminvmaster");
            dbcon.ExecuteSql(strSQLTmp);
            strSQLTmp = string.Format(strSQL, "克重", "", "GramWeight", "Iminvmaster");
            dbcon.ExecuteSql(strSQLTmp);
            strSQLTmp = string.Format(strSQL, "单张纸厚度", "", "PaperHeight", "Iminvmaster");
            dbcon.ExecuteSql(strSQLTmp);

            return true;
        }

        public bool UpdatePlanTreatCostToSecondMaterialType(DBAccess dbcon)
        {
            try
            {
                string strSQL = " update iminvmaster set PlanTreatCost=B.PlanStandardCost from rmSecondMaterialType B where SecondMaterialTypeID=B.MaterialTypeID ";
                dbcon.ExecuteSql(strSQL);
                strSQL = " update iminvmaster set PlanCost = PlanTreatCost/ TreatCost  *  MarketCost where TreatCost!=0 ";
                dbcon.ExecuteSql(strSQL);                
                return true;
            }
            catch (Exception ex)
            {
                MsgBoxEx.Show(ex.ToString());
                return false;
            }
            return true;
        }

        public bool UpdateTreatCostToSecondMaterialType(DBAccess dbcon)
        {
            try
            {
                if (!CreatTmpTable(dbcon)) return false;
                string strSQL = @"  select C.RecID as ItemRecID, B.StandardCost,C.PriceFormula from rmSecondMaterialType B 
                                    inner join iminvmaster C on B.MaterialTypeID=C.SecondMaterialTypeID
                                    And B.StandardCost !=0 and C.SecondMaterialTypeID!=''
                                ";
                DataTable dtUpdate = dbcon.GetDataTable(strSQL);
                foreach (DataRow drRow in dtUpdate.Rows)
                {
                    strSQL = "update iminvmaster set Treatcost='" + CvtFunc.ToString(drRow["StandardCost"]) + "' where recid='" + CvtFunc.ToString(drRow["ItemRecID"]) + "'";
                    dbcon.ExecuteSql(strSQL);
                    string strWKFormula = CvtFunc.ToString(drRow["PriceFormula"]);
                    if (strWKFormula != "")
                    {
                        strSQL = "select * from #rmWKCalDict";
                        DataTable dtDict = dbcon.GetDataTable(strSQL);
                        foreach (DataRow drDict in dtDict.Rows)
                        {
                            strWKFormula = strWKFormula.Replace(CvtFunc.ToString(drDict["CHNFieldName"]), CvtFunc.ToString(drDict["SYSTableName"]) + "." + CvtFunc.ToString(drDict["SYSFieldName"]));
                        }
                        strSQL = @" select top 1 {0}   from iminvmaster where recid='{1}'";
                        strSQL = string.Format(strSQL, strWKFormula, CvtFunc.ToString(drRow["ItemRecID"]));
                        try
                        {
                            DataRow drQty = dbcon.GetDataFirstRow(strSQL);
                            double dblPrice = CvtFunc.ToDouble(drQty[0]);
                            strSQL = "update iminvmaster set MarketCost='" + dblPrice.ToString("F4") + "' where recid='" + CvtFunc.ToString(drRow["ItemRecID"]) + "'";
                            dbcon.ExecuteSql(strSQL);

                            strSQL = " update iminvmaster set PlanCost=PlanTreatCost/ TreatCost  *  MarketCost ";
                            strSQL += " where recid='" + CvtFunc.ToString(drRow["ItemRecID"]) + "' and TreatCost!=0 ";
                            dbcon.ExecuteSql(strSQL);
                        }
                        catch (Exception ex)
                        {
                            //MessageBox.Show(ex.ToString());
                            MsgBoxEx.Show("表达示错误，请检查后重新输入。", SysVar.WarnTitle);
                            return false;
                        }
                    }

                }
                return true;
            }
            catch (Exception ex)
            {
                MsgBoxEx.Show(ex.ToString());
                return false;
            }
            return true;
        }
    }
}
